Snowflakeの「CREATE TABLE … USING TEMPLATE」構文を試してみた

「カスタマーストーリー」を支えるツール特集

#Snowflake

大高大輔

2021.06.15

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは！DA(データアナリティクス)事業本部インテグレーション部の大高です。

先日、SnowflakeのPreview機能として公開された、半構造化データのカラム定義検出を試したのですが、その際にCREATE TABLE ... USING TEMPLATEの利用の仕方が分からずに試せていませんでした。

こちらについて、Mineaki Motohashiさん (@mmotohas)から情報を頂いたので早速試してみたいと思います。（いつもありがとうございます！

USING TEMPLATEを使ったテーブル作成はこんな感じで実行できますー。#SnowflakeDB
create or replace table region using template(
select array_agg(object_construct(*))
from table(infer_schema(
location => '@<stage_name>/<file_name>',
file_format => '<format_name>'
)));

— Mineaki Motohashi (@mmotohas) June 14, 2021

前提条件

ユーザーステージの下記のパスに、今回利用するParquetファイルが配置済みとなります。

@~/unload/region.parquet

CREATE TABLE ... USING TEMPLATE 構文でテーブルを作成する

では、早速試してみます。

-- コンテキスト設定
USE DATABASE ootaka_sandbox_db;
USE SCHEMA public;
USE WAREHOUSE x_small_wh;

-- CREATE TABLE ... USING TEMPLATE
CREATE OR REPLACE TABLE region
USING TEMPLATE(
  SELECT
    ARRAY_AGG(OBJECT_CONSTRUCT(*))
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION => '@~/unload/region.parquet',
      FILE_FORMAT => 'ootaka_parquet_format'
    )
  )
);
╒════════════════════════════════════╕                                          
│ status                             │
╞════════════════════════════════════╡
│ Table REGION successfully created. │
╘════════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 1.738s

作成できました！テーブル定義も見てみましょう。

-- テーブル内容の表示
DESCRIBE TABLE ootaka_sandbox_db.public.region;
╒═════════════╤═══════════════════╤════════╤═══════╤═════════╤═════════════╤════════════╤═══════╤════════════╤═════════╤═════════════╕
│ name        │ type              │ kind   │ null? │ default │ primary key │ unique key │ check │ expression │ comment │ policy name │
╞═════════════╪═══════════════════╪════════╪═══════╪═════════╪═════════════╪════════════╪═══════╪════════════╪═════════╪═════════════╡
│ R_REGIONKEY │ NUMBER(38,0)      │ COLUMN │ Y     │ NULL    │ N           │ N          │ NULL  │ NULL       │ NULL    │ NULL        │
├─────────────┼───────────────────┼────────┼───────┼─────────┼─────────────┼────────────┼───────┼────────────┼─────────┼─────────────┤
│ R_NAME      │ VARCHAR(16777216) │ COLUMN │ Y     │ NULL    │ N           │ N          │ NULL  │ NULL       │ NULL    │ NULL        │
├─────────────┼───────────────────┼────────┼───────┼─────────┼─────────────┼────────────┼───────┼────────────┼─────────┼─────────────┤
│ R_COMMENT   │ VARCHAR(16777216) │ COLUMN │ Y     │ NULL    │ N           │ N          │ NULL  │ NULL       │ NULL    │ NULL        │
╘═════════════╧═══════════════════╧════════╧═══════╧═════════╧═════════════╧════════════╧═══════╧════════════╧═════════╧═════════════╛
3 Row(s) produced. Time Elapsed: 0.117s

テーブル定義も良さそうですね。

TEMPLATEについて

ところで、TEMPLATEの内容が気になるので、こちらも少し確認してみます。

SELECT
  ARRAY_AGG(OBJECT_CONSTRUCT(*))
FROM TABLE(
  INFER_SCHEMA(
    LOCATION => '@~/unload/region.parquet',
    FILE_FORMAT => 'ootaka_parquet_format'
  )
);
╒═══════════════════════════════════════════════════════════════════╕           
│ ARRAY_AGG(OBJECT_CONSTRUCT(*))                                    │
╞═══════════════════════════════════════════════════════════════════╡
│ [                                                                 │
│   {                                                               │
│     "COLUMN_NAME": "R_REGIONKEY",                                 │
│     "EXPRESSION": "$1:R_REGIONKEY::NUMBER(38, 0)",                │
│     "FILENAMES": "@~/unload/region.parquet_0_0_0.snappy.parquet", │
│     "NULLABLE": false,                                            │
│     "TYPE": "NUMBER(38, 0)"                                       │
│   },                                                              │
│   {                                                               │
│     "COLUMN_NAME": "R_NAME",                                      │
│     "EXPRESSION": "$1:R_NAME::TEXT",                              │
│     "FILENAMES": "@~/unload/region.parquet_0_0_0.snappy.parquet", │
│     "NULLABLE": false,                                            │
│     "TYPE": "TEXT"                                                │
│   },                                                              │
│   {                                                               │
│     "COLUMN_NAME": "R_COMMENT",                                   │
│     "EXPRESSION": "$1:R_COMMENT::TEXT",                           │
│     "FILENAMES": "@~/unload/region.parquet_0_0_0.snappy.parquet", │
│     "NULLABLE": true,                                             │
│     "TYPE": "TEXT"                                                │
│   }                                                               │
│ ]                                                                 │
╘═══════════════════════════════════════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.975s

ということで、JSONの配列（ARRAY型）になっているんですね。そうすると、ARRAY型を渡せばテーブルが作成できそうな気もしたので試してみます。

-- TEMPLATEにARRAY型を渡してみる
CREATE OR REPLACE TABLE region_with_json_str
USING TEMPLATE(
  SELECT
    AS_ARRAY(
      PARSE_JSON('
        [
          {
            "COLUMN_NAME": "R_REGIONKEY",
            "NULLABLE": false,
            "TYPE": "NUMBER(38, 0)"
          },
          {
            "COLUMN_NAME": "R_NAME",
            "NULLABLE": false,
            "TYPE": "TEXT"
          }
        ]
      ')
    )
);
000002 (0A000): Unsupported feature 'INFER_SCHEMA function must be used in the TEMPLATE sub-query'.

怒られてしまいました。TEMPLATEのサブクエリではINFER_SCHEMAを利用しないとダメだよ、ということですね。

ParquetファイルのデータをCOPYしてみる

データのCOPYについては、

テーブルを作成した後は、COPYコマンドでロードする際にmatch_by_column_nameオプションを指定してあげると、列名とparquetファイルの列名を自動でマッチングしてくれてロードできます！

とのことでしたので、最後にテーブル作成元にしたParquetファイルのCOPYもmatch_by_column_nameオプションを指定して試してみます。

-- ParquetファイルをCOPY
COPY INTO ootaka_sandbox_db.public.region
FROM '@~/unload/region.parquet'
FILE_FORMAT = 'ootaka_parquet_format'
MATCH_BY_COLUMN_NAME = CASE_SENSITIVE
;
╒════════════════════════════════════════════╤════════╤═════════════╤═════════════╤═════════════╤═════════════╤═════════════╤══════════════════╤═══════════════════════╤═════════════════════════╕
│ file                                       │ status │ rows_parsed │ rows_loaded │ error_limit │ errors_seen │ first_error │ first_error_line │ first_error_character │ first_error_column_name │
╞════════════════════════════════════════════╪════════╪═════════════╪═════════════╪═════════════╪═════════════╪═════════════╪══════════════════╪═══════════════════════╪═════════════════════════╡
│ unload/region.parquet_0_0_0.snappy.parquet │ LOADED │           5 │           5 │           1 │           0 │ NULL        │             NULL │                  NULL │ NULL                    │
╘════════════════════════════════════════════╧════════╧═════════════╧═════════════╧═════════════╧═════════════╧═════════════╧══════════════════╧═══════════════════════╧═════════════════════════╛
1 Row(s) produced. Time Elapsed: 2.105s

無事にCOPYできました！以下のように想定通りデータが入っています。

-- テーブルデータを確認
SELECT
  R_REGIONKEY,
  R_NAME,
  R_COMMENT
FROM
  ootaka_sandbox_db.public.region
;
╒═════════════╤═════════════╤═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│ R_REGIONKEY │ R_NAME      │ R_COMMENT                                                                                                           │
╞═════════════╪═════════════╪═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│           0 │ AFRICA      │ lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to  │
├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│           1 │ AMERICA     │ hs use ironic, even requests. s                                                                                     │
├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│           2 │ ASIA        │ ges. thinly even pinto beans ca                                                                                     │
├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│           3 │ EUROPE      │ ly final courts cajole furiously final excuse                                                                       │
├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│           4 │ MIDDLE EAST │ uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl        │
╘═════════════╧═════════════╧═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╛
5 Row(s) produced. Time Elapsed: 1.256s